import json
from typing import List

import pandas as pd

import PD.PdUtil
import PathUtil
import 去除企宽


def tt():
    # 读取 Excel 文件
    df = pd.read_csv("/temp/t.csv")

    # 定义要写入的文件名

    with open('/temp/hw.csv', 'w', encoding='utf-8') as f:
        for _, row in df.iterrows():
            row_dict = row.to_dict()
            row_json = json.dumps(row_dict, ensure_ascii=False)
            f.write(row_json + '\n')


def han(g):
    t = g[g['C.ROUTER_MODEL'] != 'UNKNOWN_DEVICE']
    # 过滤下挂路由mac列的空值
    non_empty_down_macs = g['下挂路由mac'].dropna()
    non_empty_down_macs = non_empty_down_macs[non_empty_down_macs != '']
    # 过滤路由器mac列的空值
    non_empty_router_macs = g['路由器mac'].dropna()
    non_empty_router_macs = non_empty_router_macs[non_empty_router_macs != '']
    # 过滤t['C.DEVICE_MAC']列的空值
    non_empty_device_macs = t['C.DEVICE_MAC'].dropna()
    non_empty_device_macs = non_empty_device_macs[non_empty_device_macs != '']

    s = set(non_empty_down_macs)
    s2 = set(non_empty_router_macs)
    s3 = set(non_empty_device_macs)
    sets = s | s2 | s3
    first_value = g['从网关MAC'].iloc[0]
    if isinstance(first_value, str):
        sets2 = set(first_value.split('|'))
    else:
        sets2 = set()
    return pd.Series(
        [len(s - sets2) + g['从网关数量'].iloc[0], len(s), len(s2), len(s3), "是" if len(s - sets2) > 0 else '否',
         "是" if len(sets) > len(s) else '否',
         f'1+{len(sets)}',
         f'1+{g["从网关数量"].iloc[0]}'],
        index=['实际数量', '智能组网路由器数', 'fttr路由器数', 'sa路由器数', '是否智能组网路由器', '是否自装路由器',
               '家庭网组网类型', 'fttr类型'])


def pre_实际组网数(company):
    df = pd.read_csv(fr"D:\fttr\{company}全量.csv", usecols=['用户账号', '光猫MAC'], dtype=str)
    df2 = pd.read_csv(r"/fttr/画像.csv",
                      usecols=["用户账号", "从网关MAC", "从网关数量"], dtype=str)
    df = df.merge(df2, on='用户账号', how='left')
    df2 = pd.read_csv(r"D:\Download\WeChat Files\wxid_kdchbeq2xllp22\FileStorage\File\2025-04\7智能组网路由器数量.csv",
                      dtype=str)
    df2.rename(columns={
        '光猫mac': '光猫MAC'
    }, inplace=True)
    df = df.merge(df2, on='光猫MAC', how='left')
    df2 = pd.read_csv(r"D:\Download\WeChat Files\wxid_kdchbeq2xllp22\FileStorage\File\2025-04\5SA全量用户组网.csv",
                      usecols=["A.ACCOUNT", "C.DEVICE_MAC", "C.ROUTER_MODEL"], dtype=str)
    df2.rename(columns={
        'A.ACCOUNT': '用户账号'
    }, inplace=True)
    df = df.merge(df2, on='用户账号', how='left')
    df2 = pd.read_csv(r"D:\Download\WeChat Files\wxid_kdchbeq2xllp22\FileStorage\File\2025-04\FTTR识别的路由器.csv",
                      usecols=["宽带账号", "AP MAC"], dtype=str)
    df2.rename(columns={
        '宽带账号': '用户账号',
        "AP MAC": '路由器mac'
    }, inplace=True)
    df = df.merge(df2, on='用户账号', how='left')
    PD.PdUtil.to_csv(f'/fttr/{company}下挂设备.csv', df)


def 实际组网数():
    df = pd.read_csv(f'/fttr/{company}下挂设备.csv', dtype={
        '用户账号': str,
        '从网关MAC': str
    })
    df['C.DEVICE_MAC'] = df['C.DEVICE_MAC'].str.replace(':', '')
    df['从网关数量'] = df['从网关数量'].fillna(0)
    df['从网关数量'] = df['从网关数量'].astype(int)
    res = df.groupby('用户账号')[
        ['从网关数量', '从网关MAC', 'C.DEVICE_MAC', 'C.ROUTER_MODEL', '路由器mac', '下挂路由mac']].apply(
        han).reset_index()
    # res = res.rename(columns={0: '实际数量'})
    PD.PdUtil.to_csv('/fttr/实际数量.csv', res)


def 异常ap账号():
    df = pd.read_csv(r"D:\Download\WeChat Files\wxid_kdchbeq2xllp22\FileStorage\File\2025-04\FTTR识别的路由器.csv"
                     , usecols=["宽带账号", "AP最近上线时间", "是否从FTTR"], dtype=str)
    df = df[df['是否从FTTR'] == '否']
    df['AP最近上线时间'] = pd.to_datetime(df['AP最近上线时间'])
    df['time_diff_over_5min'] = df.groupby('宽带账号')['AP最近上线时间'].transform(
        lambda x: (x.max() - x.min()).total_seconds() > 300
    )
    df.to_csv("/temp/t.csv")
    df = df[df['time_diff_over_5min'] == True]
    df = df[['宽带账号']]
    df = df.drop_duplicates()
    df.to_csv("/temp/ap异常账号.csv")


def 添加异常标识(company):
    df = pd.read_csv("/temp/ap异常账号.csv", dtype=str)
    df2 = pd.read_csv(fr"D:\fttr\{company}全量.csv", dtype=str)
    df2['ap正常'] = ~df2['用户账号'].isin(df['宽带账号'])
    df2.to_csv(fr"D:\fttr\{company}全量.csv", index=False)


def 添加实际数量(company):
    df = pd.read_csv('/fttr/实际数量.csv', dtype=str)
    df2 = pd.read_csv(rf"D:\fttr\{company}全量.csv", dtype=str)
    df2 = PD.PdUtil.drop_cols(df2, ['实际数量', '智能组网路由器数', 'fttr路由器数', 'sa路由器数', '是否智能组网路由器',
                                    '是否自装路由器', '家庭网组网类型', 'fttr类型'])
    df2 = df2.merge(df, on='用户账号', how='left')
    df2.to_csv(fr"D:\fttr\{company}全量.csv", index=False)


if __name__ == '__main__':
    company = '大兴'
    # pre_实际组网数(company)
    # 实际组网数()
    添加实际数量(company)
    添加异常标识(company)
